#region Using directives
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using webapp4U.BOL;
#endregion


namespace webapp4U.DAL
{
	 public class CompaniesDAL : BaseDAL
	 {
         #region ObjIDataReader

         /// <summary>
         /// Returns a new Companies instance filled with the DataReader's current record data 
         /// </summary>
         private Companies GetCompaniesFromReader(IDataReader dr)
         {
             Companies obj = new Companies();
             obj.CompanyID = (dr["CompanyID"] is DBNull) ? Int32.MinValue : (Int32)dr["CompanyID"];
             obj.CompanyName = (dr["CompanyName"] is DBNull) ? String.Empty : (String)dr["CompanyName"];
             obj.Address = (dr["Address"] is DBNull) ? String.Empty : (String)dr["Address"];
             obj.Email = (dr["Email"] is DBNull) ? String.Empty : (String)dr["Email"];
             obj.Phone = (dr["Phone"] is DBNull) ? String.Empty : (String)dr["Phone"];
             obj.Mobile = (dr["Mobile"] is DBNull) ? String.Empty : (String)dr["Mobile"];
             obj.Fax = (dr["Fax"] is DBNull) ? String.Empty : (String)dr["Fax"];
             obj.AddedDate = (dr["AddedDate"] is DBNull) ? DateTime.MinValue : (DateTime)dr["AddedDate"];
             obj.AddedBy = (dr["AddedBy"] is DBNull) ? String.Empty : (String)dr["AddedBy"];
             obj.DisplayOrder = (dr["DisplayOrder"] is DBNull) ? Int32.MinValue : (Int32)dr["DisplayOrder"];
             obj.IsActive = (dr["IsActive"] is DBNull) ? false : (Boolean)dr["IsActive"];
             obj.Description = (dr["Description"] is DBNull) ? String.Empty : (String)dr["Description"];
             obj.Logo = (dr["Logo"] is DBNull) ? String.Empty : (String)dr["Logo"];
             return obj;
         }
         #endregion

         /// <summary>
         /// Returns a collection of Companies with the data read from the input DataReader
         /// </summary>
         private List<Companies> GetCompaniesCollectionFromReader(IDataReader dr)
         {
             List<Companies> obj = new List<Companies>();
             while (dr.Read())
                 obj.Add(GetCompaniesFromReader(dr));
             return obj;
         }

         public int Insert(Companies obj)
         {
             using (SqlConnection myConn = GetConnectionString())
             {
                 SqlCommand myCommand = new SqlCommand("w4u_Companies_Insert", myConn);
                 myCommand.CommandType = CommandType.StoredProcedure;
                 myCommand.Parameters.Add("@CompanyID", SqlDbType.Int).Direction = ParameterDirection.Output;
                 myCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar).Value = obj.CompanyName;
                 myCommand.Parameters.Add("@Address", SqlDbType.NVarChar).Value = obj.Address;
                 myCommand.Parameters.Add("@Email", SqlDbType.VarChar).Value = obj.Email;
                 myCommand.Parameters.Add("@Phone", SqlDbType.VarChar).Value = obj.Phone;
                 myCommand.Parameters.Add("@Mobile", SqlDbType.VarChar).Value = obj.Mobile;
                 myCommand.Parameters.Add("@Fax", SqlDbType.VarChar).Value = obj.Fax;
                 myCommand.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = obj.AddedDate;
                 myCommand.Parameters.Add("@AddedBy", SqlDbType.NVarChar).Value = obj.AddedBy;
                 myCommand.Parameters.Add("@DisplayOrder", SqlDbType.Int).Value = obj.DisplayOrder;
                 myCommand.Parameters.Add("@IsActive", SqlDbType.Bit).Value = obj.IsActive;
                 myCommand.Parameters.Add("@Description", SqlDbType.NText).Value = obj.Description;
                 myCommand.Parameters.Add("@Logo", SqlDbType.NVarChar).Value = obj.Logo;

                 int ret = myCommand.ExecuteNonQuery();
                 return (int)myCommand.Parameters["@CompanyID"].Value;
             }
         }

         public bool Update(Companies obj)
         {
             using (SqlConnection myConn = GetConnectionString())
             {
                 SqlCommand myCommand = new SqlCommand("w4u_Companies_Update", myConn);
                 myCommand.CommandType = CommandType.StoredProcedure;
                 myCommand.Parameters.Add("@CompanyID", SqlDbType.Int).Value = obj.CompanyID;
                 myCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar).Value = obj.CompanyName;
                 myCommand.Parameters.Add("@Address", SqlDbType.NVarChar).Value = obj.Address;
                 myCommand.Parameters.Add("@Email", SqlDbType.VarChar).Value = obj.Email;
                 myCommand.Parameters.Add("@Phone", SqlDbType.VarChar).Value = obj.Phone;
                 myCommand.Parameters.Add("@Mobile", SqlDbType.VarChar).Value = obj.Mobile;
                 myCommand.Parameters.Add("@Fax", SqlDbType.VarChar).Value = obj.Fax;
                 myCommand.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = obj.AddedDate;
                 myCommand.Parameters.Add("@AddedBy", SqlDbType.NVarChar).Value = obj.AddedBy;
                 myCommand.Parameters.Add("@DisplayOrder", SqlDbType.Int).Value = obj.DisplayOrder;
                 myCommand.Parameters.Add("@IsActive", SqlDbType.Bit).Value = obj.IsActive;
                 myCommand.Parameters.Add("@Description", SqlDbType.NText).Value = obj.Description;
                 myCommand.Parameters.Add("@Logo", SqlDbType.NVarChar).Value = obj.Logo;

                 int ret = myCommand.ExecuteNonQuery();
                 return (ret == 1);
             }
         }


	     public bool Delete(Int32 CompanyID)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("w4u_Companies_Delete", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@CompanyID", SqlDbType.Int).Value = CompanyID;

		     int ret = myCommand.ExecuteNonQuery();
		     return (ret == 1);
		     }
	     }

	     /// <summary>
	     /// Retrieves the w4u_Companies with the specified ID
	     /// </summary>
	     public Companies GetCompaniesByID(Int32 CompanyID)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("w4u_Companies_GetCompaniesByID", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@CompanyID", SqlDbType.Int).Value = CompanyID;

		     SqlDataReader dr = myCommand.ExecuteReader();
		     return (dr.Read() ? GetCompaniesFromReader(dr) : null);
		     }
	     }

	     //Get All Companies
	     public List<Companies> GetAllCompanies(int PageIndex, int PageSize)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("w4u_Companies_GetAllCompanies", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
		     myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
		     SqlDataReader dr = myCommand.ExecuteReader();
		     return GetCompaniesCollectionFromReader(dr);
		     }
	     }

	     // Get Count All records of Table  Companies
	     public int GetCountCompanies()
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("w4u_Companies_GetCountCompanies", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     return (int)myCommand.ExecuteScalar();
		     }
	     }



	 }
}
